package br.ucb.modelo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import br.ucb.exceptions.PersistenceException;
import br.ucb.modelo.negocio.User;
import br.ucb.service.ListFactory;
import br.ucb.service.Servicos;

public class UserDAO implements DAO <User>{

	@Override
	public int insert(User user) throws PersistenceException {
		Connection con;
		int retorno = 0;
		PreparedStatement stmt;
		String sql = "insert into usuario (nome, cpf, sexo, email, dataNascimento,"
				+ " estado, cidade, complemento, ddd, numero, login, senha)"
				+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, user.getNome());
			stmt.setString(2, user.getCpf());
			stmt.setString(3, user.getSexo().toString());
			stmt.setString(4, user.getEmail());
			java.sql.Date dta = new java.sql.Date(user.getDataNascimento().getTime());
			stmt.setDate(5, dta);
			stmt.setString(6, user.getEndereco().getEstado());
			stmt.setString(7, user.getEndereco().getCidade());
			stmt.setString(8, user.getEndereco().getComplemento());
			stmt.setInt(9, user.getTelefone().getDdd());
			stmt.setString(10, user.getTelefone().getNumero());
			stmt.setString(11, user.getLogin());
			stmt.setString(12, user.getSenha());
			retorno = stmt.executeUpdate();
			stmt.close();
		} catch(SQLException e) {
			e.printStackTrace();
			ConnectionFactory.close();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return retorno;
	}

	@Override
	public int update(User user) throws PersistenceException {
		Connection con;
		int retorno = 0;
		PreparedStatement stmt;
		String sql = "update usuario set nome=?, sexo=?, dataNascimento = ?,"
				+ " estado = ?, cidade = ?, complemento = ?, "
				+ "ddd = ? , numero = ?, senha = ?"
				+ "where id=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, user.getNome());
			stmt.setString(2, user.getSexo().toString());
			java.sql.Date dta = new java.sql.Date(user.getDataNascimento().getTime());
			stmt.setDate(3, dta);
			stmt.setString(4, user.getEndereco().getEstado());
			stmt.setString(5, user.getEndereco().getCidade());
			stmt.setString(6, user.getEndereco().getComplemento());
			stmt.setInt(7, user.getTelefone().getDdd());
			stmt.setString(8, user.getTelefone().getNumero());
			stmt.setString(9, user.getSenha());
			stmt.setLong(10, user.getId());
			retorno = stmt.executeUpdate();
			stmt.close();
		} catch(SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return retorno;
	}

	@Override
	public int delete(Long id) throws PersistenceException {
		Connection con;
		int retorno = 0;
		PreparedStatement stmt;
		String sql = "delete from usuario where id=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setLong(1, id);
			retorno = stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return retorno;
	}

	@Override
	public User findByID(Long id) throws PersistenceException {
		Connection con;
		User user = null;
		PreparedStatement stmt;
		String sql = "select * from usuario where id=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();
			if(rs.first())
			   user = Servicos.putUser(rs);
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return user;
	}

	@Override
	public List<User> findAll() throws PersistenceException {
		Connection con;
		List <User> users = ListFactory.getDefaultListUser();
		Statement stmt;
		String sql = "select * from usuario";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
			   User user = Servicos.putUser(rs);
			   users.add(user);
			}
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return users;
	}
	
	public User findByEmail(String email) throws PersistenceException {
		Connection con;
		User user = null;
		PreparedStatement stmt;
		String sql = "select * from usuario where email=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, email);
			ResultSet rs = stmt.executeQuery();
			if(rs.first())
			   user = Servicos.putUser(rs);
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return user;
	}
	
	public User findByCpf(String cpf) throws PersistenceException {
		Connection con;
		User user = null;
		PreparedStatement stmt;
		String sql = "select * from usuario where cpf=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, cpf);
			ResultSet rs = stmt.executeQuery();
			if(rs.first())
			   user = Servicos.putUser(rs);
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return user;
	}
	
	public User findByLogin(String login) throws PersistenceException {
		Connection con;
		User user = null;
		PreparedStatement stmt;
		String sql = "select * from usuario where login=?";
		try {
			con = ConnectionFactory.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, login);
			ResultSet rs = stmt.executeQuery();
			if(rs.first())
			   user = Servicos.putUser(rs);
			stmt.close();
		} catch (SQLException e) {
			ConnectionFactory.close();
			e.printStackTrace();
			throw new PersistenceException();
		}
		ConnectionFactory.close();
		return user;
	}
   
}
